package jdbc;

import Util.DBUtil;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author 汤舒青
 * @Version 1.0
 */


public class SubjectDao implements Serializable {
    public int add(Subject s) {
        Connection connection = null;
        PreparedStatement st = null;
        try {
            connection = DBUtil.getConnection();
            String sql = "insert into s_subject(subjectname,classhour,gradeid) values(?,?,?)";
            System.out.println(sql);
            st = connection.prepareStatement(sql);
            st.setString(1, s.getSubjectname());
            st.setInt(2, s.getClasshour());
            st.setLong(3, s.getGradeid());

            return st.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(null, st, connection);
        }
        return 0;
    }

    public int update(Subject s) {
        Connection con = null;
        PreparedStatement pe = null;


        String sql = "update s_subject set subjectname=?,classhour=?,gradeid=? where id=?";
        System.out.println(sql);
        try {
            con = DBUtil.getConnection();
            pe = con.prepareStatement(sql);
            pe.setString(1, s.getSubjectname());
            pe.setInt(2, s.getClasshour());
            pe.setLong(3, s.getGradeid());
            return pe.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(pe);
            DBUtil.close(con);
        }

        return 0;
    }

    public int del(long id) {
        Connection con = null;
        PreparedStatement pe = null;

        String sql = "delete FROM  `s_subject` where `id`=?";
        System.out.println(sql);
        try {
            pe = con.prepareStatement(sql);
            pe.setLong(1, id);
            return pe.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(pe);
            DBUtil.close(con);
        }

        return 0;
    }

    //主键找对象
    public Subject get(long id) {

        PreparedStatement pe = null;
        ResultSet res = null;
        Connection con = DBUtil.getConnection();
        String sql = "select id,subjectname,classhour,gradeid  FROM  s_subject where id = ?";
        System.out.println(sql);
        try {

            pe = con.prepareStatement(sql);
            pe.setLong(1, id);
            res = pe.executeQuery();
            while (res.next()) {
                return new Subject(res.getLong(1), res.getString(2), res.getInt(3), res.getLong(4));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(res);
            DBUtil.close(pe);
            DBUtil.close(con);
        }


        return null;
    }

    //查询全部对象
    public List<Subject> findALL() {
        List<Subject> list = new ArrayList<>();
        Connection con = DBUtil.getConnection();
        PreparedStatement pe = null;
        ResultSet res = null;
        String sql = "select id,subjectname,classhour,gradeid FROM  `s_Subject`";
        System.out.println(sql);
        try {
            pe = con.prepareStatement(sql);
            res = pe.executeQuery();
            while (res.next()) {
                list.add(new Subject(res.getInt(1), res.getString(2), res.getInt(3), res.getLong(4)));
                return list;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(res);
            DBUtil.close(pe);
            DBUtil.close(con);
        }

        return null;
    }

    public List<Subject> search(int pageNo, int pageSize) {
        String sql = "select * from s_subject limit?,? order by id asc ";
        List<Subject> datas = new ArrayList<>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            con = DBUtil.getConnection();
            ps = con.prepareStatement(sql);
            ps.setInt(1, (pageNo - 1) * pageSize);
            ps.setInt(2, pageSize);
            rs = ps.executeQuery();
            for (int i = 1; i <= pageSize && rs.next(); i++) {
                datas.add(new Subject(rs.getLong("id"), rs.getString("subjectname"), rs.getInt("classhour"), rs.getLong("gradeid")));

            }

        } catch (SQLException e) {

            e.printStackTrace();
        } finally {
            DBUtil.close(rs, ps, con);
        }
        return datas;
    }

}

